package schoolsource.sql;

import java.util.logging.Level;

import schoolsource.model.Item;
import schoolsource.util.Encrypter;
import schoolsource.util.SchoolLogger;

/**
 * 
 * @author Steven Steele
 * Copyright 2003
 *
 * A table representation that performs database updates and queries. This extension is
 * specific to the MySQL database.
 */
public class MySQLAccessor extends DBSQLAccessor {

	public static final String KEY = "H4IS943d?";

	public MySQLAccessor(String newDataSource, String newTableName) {
		super(newDataSource, newTableName);
	}

	public String getDBFormattedInteger(String trueString) {
		return trueString;
	}

	public String removeDBTagsFromInteger(String dbString) {
		return dbString;
	}

	public String getDBFormattedDouble(String trueString) {
		return trueString;
	}

	public String removeDBTagsFromDouble(String dbString) {
		return dbString;
	}

	public String getDBFormattedDate(String trueString) {
		String returnVal = "null";
		returnVal = "\'" + trueString + "\'";
		return returnVal;
	}

	public String removeDBTagsFromDate(String dbString) {
		return dbString;
	}

	public String getDBFormattedString(String trueString) {
		StringBuffer returnVal = new StringBuffer();
		int apostropheLoc;
		int temp = 0;
		if (trueString != null) {
			for (int i = 0; i < trueString.length(); i++) {
				switch (trueString.charAt(i)) {
					case '\0' :
						returnVal.append("\\0");
						break;
					case '\'' :
						returnVal.append("\\'");
						break;
					case '\"' :
						returnVal.append("\\\"");
						break;
					case '\b' :
						returnVal.append("\\b");
						break;
					case '\n' :
						returnVal.append("\\n");
						break;
					case '\r' :
						returnVal.append("\\r");
						break;
					case '\t' :
						returnVal.append("\\t");
						break;
					case '\\' :
						returnVal.append("\\\\");
						break;
					case '%' :
						returnVal.append("\\%");
						break;
						// documentation states that the underscore must be escaped.
						// imperical evidence suggests otherwise.
						//				case '_':	returnVal.append("\\_");
						//							break;
					default :
						returnVal.append(trueString.charAt(i));
				}
			}
		}
		return "'" + returnVal + "'";
	}

	public String removeDBTagsFromString(String dbString) {
		return dbString;
	}

	public String getDBFormattedPassword(String trueString) {
		StringBuffer returnVal = new StringBuffer();
		returnVal =
			new StringBuffer(
				"ENCODE('"
					+ Encrypter.encrypt(trueString)
					+ "', '"
					+ KEY
					+ "')");
		return returnVal.toString();
	}

	public String removeDBTagsFromPassword(String dbString) {
		return dbString;
	}

	public String getDBFormattedBoolean(String trueString) {
		return "'" + trueString.toUpperCase() + "'";
	}

	public String removeDBTagsFromBoolean(String dbString) {
		String returnVal = "TRUE";
		if (dbString.equalsIgnoreCase("FALSE") || dbString.equals("0")) {
			returnVal = "FALSE";
		}
		return returnVal;
	}

	/**
	 * Tests the database methods.
	 *
	 */
	private void doTest() {
		SchoolLogger.logger.log(Level.INFO, "Creating test data set");
		int i = 1;
		Item id =
			new schoolsource.model.StringItem(
				"TESTER_ID",
				new Integer(i),
				false);
		Item text =
			new schoolsource.model.StringItem("TESTER_TXT", "Bob Weed", false);
		Item date =
			new schoolsource.model.DateItem("TESTER_DATE", "09/11/01", false);
		Item cur =
			new schoolsource.model.StringItem("TESTER_CUR", "55555.50", false);
		SchoolLogger.logger.log(Level.INFO, "Attempting saves");
		for (; i < 5; i++) {
			SchoolLogger.logger.log(Level.INFO, "Saving " + i);
			id.setTrueValue(new Integer(i));
			save(new Item[] { id, text, date, cur }, new Item[] { id }, AND);
			SchoolLogger.logger.log(Level.INFO, "Saved " + i);
		}
		SchoolLogger.logger.log(Level.INFO, "Finished saves");
		SchoolLogger.logger.log(Level.INFO, "Attempting load");
		id.setTrueValue(new Integer(2));
		load(new Item[] { id, text, date, cur }, new Item[] { id }, AND);
		SchoolLogger.logger.log(Level.INFO, "Finished load:");
		SchoolLogger.logger.log(Level.INFO, "id = " + id.toString());
		SchoolLogger.logger.log(Level.INFO, "text = " + text.toString());
		SchoolLogger.logger.log(Level.INFO, "date = " + date.toString());
		SchoolLogger.logger.log(Level.INFO, "cur = " + cur.toString());
	}

	/**
	 * Runs a test on the database methods.
	 * @param args
	 */
	public static void main(String args[]) {
		SchoolLogger.logger.log(Level.INFO, "Starting MySQLAccessor test.");
		MySQLAccessor test = new MySQLAccessor("jdbc:caucho:tester", "Test");
		test.doTest();
		SchoolLogger.logger.log(Level.INFO, "Finished MySQLAccessor test.");
	}
}
